﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using MHYS.DEMO.Model;
namespace MHYS.DEMO.DAL
{
    public class DBHelper
    {


        protected static SqlConnection conn;
        protected static string sql = ConfigurationManager.ConnectionStrings["Demo"].ToString();
        protected static void init() {
            if (conn == null) {
                conn = new SqlConnection(sql);
            }
            if (conn.State == ConnectionState.Closed) {
                conn.Open();
            }
            if (conn.State == ConnectionState.Broken) {
                conn.Close();
                conn.Open();
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>数据表</returns>
        public static DataTable SelectDatatable(string sql){
            init();
            SqlDataAdapter sqlData = new SqlDataAdapter(sql,conn);
            DataTable dataTable = new DataTable();
            sqlData.Fill(dataTable);
            conn.Close();
            return dataTable;
           }

        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql">增删改语句</param>
        /// <returns>是否成功</returns>
        public static bool ExecNonQuery(string sql) {
            init();
            SqlCommand command = new SqlCommand(sql,conn);
            int a = command.ExecuteNonQuery();
            conn.Close();
            return a > 0;
        }

        /// <summary>
        /// 输出标识符的存储过程
        /// </summary>
        /// <param name="proName">存储过程名称</param>
        /// <param name="paras">参数</param>
        /// <returns></returns>
        public static int ExecuteProdure(string proName, SqlParameter[] paras) {
            init();
            SqlCommand cmd = new SqlCommand(proName,conn);
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < paras.Length; i++) {
                cmd.Parameters.Add(paras[i]);
            }
            cmd.ExecuteNonQuery();
            conn.Close();
            return int.Parse(cmd.Parameters["@OrderId"].Value.ToString());
        }


    }
}
